
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[MatchEvents_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchEvents_Insert]

IF OBJECT_ID(N'[dbo].[MatchEvents_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchEvents_Update]

IF OBJECT_ID(N'[dbo].[MatchEvents_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchEvents_Delete]

IF OBJECT_ID(N'[dbo].[MatchEvents_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchEvents_Select]

--endregion

GO


--region [dbo].[MatchEvents_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchEvents_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdMatchEvent int = null,
	@IdMatch int = null,
	@Team nvarchar(50) = null,
	@Minute nvarchar(20) = null,
	@Score nvarchar(10) = null,
	@EventType nvarchar(50) = null,
	@Description nvarchar(1000) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[MatchEvents]
					WHERE
					(
						(@IdMatchEvent is null OR [MatchEvents].[IdMatchEvent] = @IdMatchEvent)
						AND (@IdMatch is null OR [MatchEvents].[IdMatch] = @IdMatch)
						AND (@Team is null OR [MatchEvents].[Team] like @Team)
						AND (@Minute is null OR [MatchEvents].[Minute] like @Minute)
						AND (@Score is null OR [MatchEvents].[Score] like @Score)
						AND (@EventType is null OR [MatchEvents].[EventType] like @EventType)
						AND (@Description is null OR [MatchEvents].[Description] like @Description)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[MatchEvents].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdMatchEvent' and @SortType = 1 THEN [MatchEvents].[IdMatchEvent] END ASC,
						CASE WHEN @SortBy = 'IdMatchEvent' and @SortType = 0 THEN [MatchEvents].[IdMatchEvent] END DESC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 1 THEN [MatchEvents].[IdMatch] END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 0 THEN [MatchEvents].[IdMatch] END DESC,
						CASE WHEN @SortBy = 'Team' and @SortType = 1 THEN [MatchEvents].[Team] END ASC,
						CASE WHEN @SortBy = 'Team' and @SortType = 0 THEN [MatchEvents].[Team] END DESC,
						CASE WHEN @SortBy = 'Minute' and @SortType = 1 THEN [MatchEvents].[Minute] END ASC,
						CASE WHEN @SortBy = 'Minute' and @SortType = 0 THEN [MatchEvents].[Minute] END DESC,
						CASE WHEN @SortBy = 'Score' and @SortType = 1 THEN [MatchEvents].[Score] END ASC,
						CASE WHEN @SortBy = 'Score' and @SortType = 0 THEN [MatchEvents].[Score] END DESC,
						CASE WHEN @SortBy = 'EventType' and @SortType = 1 THEN [MatchEvents].[EventType] END ASC,
						CASE WHEN @SortBy = 'EventType' and @SortType = 0 THEN [MatchEvents].[EventType] END DESC,
						CASE WHEN @SortBy = 'Description' and @SortType = 1 THEN [MatchEvents].[Description] END ASC,
						CASE WHEN @SortBy = 'Description' and @SortType = 0 THEN [MatchEvents].[Description] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[MatchEvents]
			Where 
			(
				(@IdMatchEvent is null OR [MatchEvents].[IdMatchEvent] = @IdMatchEvent)
				AND (@IdMatch is null OR [MatchEvents].[IdMatch] = @IdMatch)
				AND (@Team is null OR [MatchEvents].[Team] like @Team)
				AND (@Minute is null OR [MatchEvents].[Minute] like @Minute)
				AND (@Score is null OR [MatchEvents].[Score] like @Score)
				AND (@EventType is null OR [MatchEvents].[EventType] like @EventType)
				AND (@Description is null OR [MatchEvents].[Description] like @Description)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[MatchEvents_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[MatchEvents_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchEvents_Insert]
	@IdMatchEvent int OUTPUT,
	@IdMatch int,
	@Team nvarchar(50),
	@Minute nvarchar(20),
	@Score nvarchar(10),
	@EventType nvarchar(50),
	@Description nvarchar(1000)

AS


INSERT INTO [dbo].[MatchEvents] 
(
	[IdMatch],
	[Team],
	[Minute],
	[Score],
	[EventType],
	[Description]
)
VALUES 
(
	@IdMatch,
	@Team,
	@Minute,
	@Score,
	@EventType,
	@Description
)

SET @IdMatchEvent = SCOPE_IDENTITY()

--end [dbo].[MatchEvents_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[MatchEvents_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchEvents_Update]
	@IdMatchEvent int,
	@IdMatch int,
	@Team nvarchar(50),
	@Minute nvarchar(20),
	@Score nvarchar(10),
	@EventType nvarchar(50),
	@Description nvarchar(1000)
AS


UPDATE [dbo].[MatchEvents] SET
	[IdMatch] = @IdMatch,
	[Team] = @Team,
	[Minute] = @Minute,
	[Score] = @Score,
	[EventType] = @EventType,
	[Description] = @Description
WHERE
	[IdMatchEvent] = @IdMatchEvent

--end [dbo].[MatchEvents_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[MatchEvents_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchEvents_Delete]
	@IdMatchEvent int
AS


DELETE FROM [dbo].[MatchEvents]
WHERE
(
	[IdMatchEvent] = @IdMatchEvent
)

--end [dbo].[MatchEvents_Delete]
--endregion

GO
--=========================================================================================--

